package com.pennywise.mapper;

import com.pennywise.model.CategoryStats;
import com.pennywise.model.Transaction;
import com.pennywise.model.MonthlyStats;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface TransactionMapper {
    
    @Insert("""
        INSERT INTO "transaction" (amount, type, category, description, transaction_time, create_time)
        VALUES (#{amount}, #{type}, #{category}, #{description}, #{transactionTime}, #{createTime})
    """)
    void insert(Transaction transaction);
    
    @Select("""
        SELECT * FROM "transaction" 
        WHERE transaction_time BETWEEN #{startTime} AND #{endTime}
        ORDER BY transaction_time DESC
    """)
    List<Transaction> findByTimeRange(LocalDateTime startTime, LocalDateTime endTime);
    
    @Select("SELECT * FROM \"transaction\" WHERE id = #{id}")
    Transaction findById(Long id);
    
    @Update("""
        UPDATE "transaction" SET
        amount = #{amount},
        type = #{type},
        category = #{category},
        description = #{description},
        transaction_time = #{transactionTime}
        WHERE id = #{id}
    """)
    void update(Transaction transaction);
    
    @Delete("DELETE FROM \"transaction\" WHERE id = #{id}")
    void deleteById(Long id);
    
    @Select("""
        SELECT category, SUM(amount) as total 
        FROM "transaction" 
        WHERE type = #{type} AND transaction_time BETWEEN #{startTime} AND #{endTime}
        GROUP BY category
    """)
    List<CategoryStats> getCategoryStats(String type, LocalDateTime startTime, LocalDateTime endTime);
    
    @Select("""
        SELECT 
            strftime('%Y', transaction_time) as year,
            strftime('%m', transaction_time) as month,
            SUM(CASE WHEN type = '收入' THEN amount ELSE 0 END) as totalIncome,
            SUM(CASE WHEN type = '支出' THEN amount ELSE 0 END) as totalExpense,
            SUM(CASE WHEN type = '收入' THEN amount ELSE -amount END) as balance
        FROM "transaction" 
        WHERE transaction_time BETWEEN #{startTime} AND #{endTime}
        GROUP BY strftime('%Y', transaction_time), strftime('%m', transaction_time)
        ORDER BY year DESC, month DESC
    """)
    List<MonthlyStats> getMonthlyStats(LocalDateTime startTime, LocalDateTime endTime);
} 